package com.dshore.messagecenter.dao.statistics;

import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.dshore.messagecenter.model.system.SysDict;

@Repository
public class MessageDao {
	
	@Autowired
	JdbcTemplate jdbcTemplate;

	/**查询所有状态*/
	public List<Map<String, Object>> getMsgStatus() {
		String sql = " SELECT code, name from sys_dict "
				+ " where dict_type = 'msgStatus' ";
		return jdbcTemplate.queryForList(sql);
	}

	/**
	 * 根据状态编码查值状态
	 * @param status
	 * @return
	 */
	public String queryStatus(String status) {
		StringBuffer sql = new StringBuffer().append("select name from sys_dict "
				+ "where dict_type = 'msgStatus' and code=?");
		return jdbcTemplate.queryForObject(sql.toString(), String.class, status);
	}
	
	/**
	 * 查询消息流量统计界面查询下拉框中的查询周期
	 * @return
	 */
	public List<SysDict> querryPeriod() {
		String sql="SELECT * FROM T_SYS_DICT  WHERE T_SYS_DICT.DEL_FLAG=0 and T_SYS_DICT.DICT_TYPE='period_type' order by T_SYS_DICT.seq";
		RowMapper<SysDict> userRowMapper = new BeanPropertyRowMapper<>(SysDict.class);
		return jdbcTemplate.query(sql, userRowMapper);
		
	}
	
	/**
	 * 插入指标实时记录
	 * @param rabbitMqMonitor
	 * @return
	 */
	public void insertCurrentKPI(List<Object[]> rabbitMqMonitor) {
		String sql = "insert into T_MR_KPI_RECORD_CURRENT (ID, KPI_CODE, KPI_VALUE, OBJ_TYPE, OBJ, CREATED_TIME, MODIFIED_TIME) "
				+ "values (?, (select DICT_CODE from T_SYS_DICT where DICT_NAME = ? and DICT_TYPE = 'kpi_type'), ?,  "
				+ "(select DICT_CODE from T_SYS_DICT where DICT_NAME = ? and DICT_TYPE = 'obj_type'), ?, ?, ?)";
		jdbcTemplate.batchUpdate(sql, rabbitMqMonitor);
	}
	
	/**
	 * 根据给定的本日字符串时间获取当日的消息流量数据
	 * @version 8/27
	 */
	public List<Map<String, Object>>getTodayMessageFlowByTime(String riqi){
		StringBuffer sql=new StringBuffer();
		sql.append("select TO_CHAR(CREATED_TIME,'hh24') as a,SUM(T_MR_KPI_RECORD_CURRENT.KPI_VALUE) as b");
		sql.append(" from T_MR_KPI_RECORD_CURRENT");
		sql.append(" WHERE T_MR_KPI_RECORD_CURRENT.KPI_CODE=(select T_SYS_DICT.DICT_CODE from T_SYS_DICT where T_SYS_DICT.DICT_TYPE='kpi_type' and T_SYS_DICT.DICT_NAME='消息流量')");
		sql.append(" AND TO_CHAR(CREATED_TIME,'yyyy-mm-dd hh24:mi:ss') >=\'");		
		sql.append(riqi+"00:00:00\'");
		sql.append(" AND  TO_CHAR(CREATED_TIME,'yyyy-mm-dd hh24:mi:ss') <=\'");
		sql.append(riqi+"23:59:59\'");	
		sql.append(" GROUP BY TO_CHAR(CREATED_TIME,'hh24')");
		sql.append(" ORDER BY TO_CHAR(CREATED_TIME,'hh24')");
		return jdbcTemplate.queryForList(sql.toString());
	}
	
	/**
	 * 根据给定的本日字符串时间获取单个主题队列的消息流量
	 * 表示单个主题队列，根据单个主题队列的obj的值查询他们的KPI_VALUE
	 * @version 8/28
	 */
	public List<Map<String,Object>> getTodayMessageFlowByTopic(String riqi) {
		StringBuffer sql=new StringBuffer();
		sql.append("select AA.TOPIC_QUEUE_NAME AS NAME,bb.sum  AS VALUE from \r\n" + 
				"(select top.TOPIC_QUEUE_NAME , top.topic_queue_no\r\n" + 
				"from T_MD_TOPIC top) AA,\r\n" + 
				"(select t.obj,sum(t.KPI_VALUE) as sum\r\n" + 
				"from T_MR_KPI_RECORD_CURRENT t\r\n" + 
				"where t.KPI_CODE=(select T_SYS_DICT.DICT_CODE from T_SYS_DICT where T_SYS_DICT.DICT_TYPE='kpi_type' and T_SYS_DICT.DICT_NAME='消息流量')\r\n" + 
				"and t.OBJ_TYPE=(select T_SYS_DICT.DICT_CODE from T_SYS_DICT where T_SYS_DICT.DICT_TYPE='obj_type' and T_SYS_DICT.DICT_NAME='主题队列')\r\n" + 
				"AND TO_CHAR(CREATED_TIME,'yyyy-mm-dd hh24:mi:ss') >=\'"+riqi+"00:00:00\' \r\n" + 
				"AND TO_CHAR(CREATED_TIME,'yyyy-mm-dd hh24:mi:ss') <=\'"+riqi+"23:59:59\' \r\n" + 
				"group by t.OBJ) BB\r\n" + 
				"WHERE AA.topic_queue_no=BB.OBJ");
		return jdbcTemplate.queryForList(sql.toString());
	}
	
	/**
	 * 根据下拉框中option标签的id来查询过去七天的消息流量数据--按照时间分组
	 * @version 8/27
	 */
	public List<Map<String,Object>> getMessageFlowFromLastSevenDaysByTime() {
		StringBuffer sql=new StringBuffer();
		sql.append("select days.createdTime1 as day,nvl(a,0) as value");
		sql.append(" from(select to_char(SYSDATE-LEVEL+1,'yyyy-mm-dd') as createdTime1 from dual connect by level<=7) days");
		sql.append(" LEFT JOIN(");
		sql.append(" SELECT TO_CHAR(CREATED_TIME,'yyyy-mm-dd') AS createdTime2,Sum(KPI_VALUE) AS a");
		sql.append(" FROM T_MR_KPI_RECORD_CURRENT ");
		sql.append(" WHERE KPI_CODE=(select T_SYS_DICT.DICT_CODE from T_SYS_DICT where T_SYS_DICT.DICT_TYPE='kpi_type' and T_SYS_DICT.DICT_NAME='消息流量') ");
		sql.append(" GROUP BY TO_CHAR(CREATED_TIME,'yyyy-mm-dd')) m ");
		sql.append(" ON days.createdTime1=m.createdTime2");
		sql.append(" ORDER BY days.createdTime1");
		return jdbcTemplate.queryForList(sql.toString());
	}
	
	/**
	 *  根据下拉框中option标签的id来查询过去七天的消息流量数据--按照主题队列分组
	 *  @version 8/28
	 */
	public List<Map<String,Object>> getMessageFlowFromLastSevenDaysByTopic(String riqi,String qitian) {
		StringBuffer sql=new StringBuffer();
		sql.append("select AA.TOPIC_QUEUE_NAME AS NAME,bb.sum  AS VALUE from \r\n" + 
				"(select top.TOPIC_QUEUE_NAME , top.topic_queue_no\r\n" + 
				"from T_MD_TOPIC top) AA,\r\n" + 
				"(select t.obj,sum(t.KPI_VALUE) as sum\r\n" + 
				"from T_MR_KPI_RECORD_CURRENT t\r\n" + 
				"where t.KPI_CODE=(select T_SYS_DICT.DICT_CODE from T_SYS_DICT where T_SYS_DICT.DICT_TYPE='kpi_type' and T_SYS_DICT.DICT_NAME='消息流量')\r\n" + 
				"and t.OBJ_TYPE=(select T_SYS_DICT.DICT_CODE from T_SYS_DICT where T_SYS_DICT.DICT_TYPE='obj_type' and T_SYS_DICT.DICT_NAME='主题队列')\r\n" + 
				"AND TO_CHAR(CREATED_TIME,'yyyy-mm-dd hh24:mi:ss') >=\'"+qitian+"00:00:00\'\r\n" + 
				"AND TO_CHAR(CREATED_TIME,'yyyy-mm-dd hh24:mi:ss') <=\'"+riqi+"23:59:59\'\r\n" + 
				"group by t.OBJ) BB\r\n" + 
				"WHERE AA.topic_queue_no=BB.OBJ");
		return jdbcTemplate.queryForList(sql.toString());
	}
	
	/**
	 * 根据下拉框中option标签的id来查询过去30天的消息流量数据--按照时间分组
	 * @version 8/27
	 */
	public List<Map<String,Object>> getMessageFlowFromLastMonthDaysByTime() {
		StringBuffer sql=new StringBuffer();
		sql.append("select days.createdTime1 as day,nvl(a,0) as value");
		sql.append(" from(select to_char(SYSDATE-LEVEL+1,'yyyy-mm-dd') as createdTime1 from dual connect by level<=30) days");
		sql.append(" LEFT JOIN(");
		sql.append(" SELECT TO_CHAR(CREATED_TIME,'yyyy-mm-dd') AS createdTime2,Sum(KPI_VALUE) AS a");
		sql.append(" FROM T_MR_KPI_RECORD_CURRENT ");
		sql.append(" WHERE KPI_CODE=(select T_SYS_DICT.DICT_CODE from T_SYS_DICT where T_SYS_DICT.DICT_TYPE='kpi_type' and T_SYS_DICT.DICT_NAME='消息流量') ");
		sql.append(" GROUP BY TO_CHAR(CREATED_TIME,'yyyy-mm-dd')) m ");
		sql.append(" ON days.createdTime1=m.createdTime2");
		sql.append(" ORDER BY days.createdTime1");
		return jdbcTemplate.queryForList(sql.toString());
	}
	
	/**
	 *  根据下拉框中option标签的id来查询过去30天的消息流量数据--按照主题队列分组
	 *  @version 8/28
	 */
	public List<Map<String,Object>> getMessageFlowFromLastMonthByTopic(String riqi,String qitian) {
		StringBuffer sql=new StringBuffer();
		sql.append("select AA.TOPIC_QUEUE_NAME AS NAME,bb.sum  AS VALUE from \r\n" + 
				"(select top.TOPIC_QUEUE_NAME , top.topic_queue_no\r\n" + 
				"from T_MD_TOPIC top) AA,\r\n" + 
				"(select t.obj,sum(t.KPI_VALUE) as sum\r\n" + 
				"from T_MR_KPI_RECORD_CURRENT t\r\n" + 
				"where t.KPI_CODE=(select T_SYS_DICT.DICT_CODE from T_SYS_DICT where T_SYS_DICT.DICT_TYPE='kpi_type' and T_SYS_DICT.DICT_NAME='消息流量')\r\n" + 
				"and t.OBJ_TYPE=(select T_SYS_DICT.DICT_CODE from T_SYS_DICT where T_SYS_DICT.DICT_TYPE='obj_type' and T_SYS_DICT.DICT_NAME='主题队列')\r\n" + 
				"AND TO_CHAR(CREATED_TIME,'yyyy-mm-dd hh24:mi:ss') >=\'"+qitian+"00:00:00\'\r\n" + 
				"AND TO_CHAR(CREATED_TIME,'yyyy-mm-dd hh24:mi:ss') <=\'"+riqi+"23:59:59\'\r\n" + 
				"group by t.OBJ) BB\r\n" + 
				"WHERE AA.topic_queue_no=BB.OBJ");
		return jdbcTemplate.queryForList(sql.toString());
	}
}
